﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace HostAssistantCore
{
    public  class SqlServerManager
    {
        /// <summary>
        /// 获取所有数据库信息
        /// </summary>
        /// <returns></returns>
        public static void GetMySqlInfos(string sqlpass, string sqlname)
        {
            DataTable sqlinfos = new DataTable();//存储对象
            SqlServerInfo sqlinfo = null;//单个数据库信息
            List<string> csqlservers = new List<string>();//sql数据库
            List<string> accessmysqls = new List<string>();//access数据库
            //测试连接
            SqlDbHelper.Initialize("master", sqlname, sqlpass);
            try
            {
               
               // 获取所有数据库名
               
                string get_schema_name_sql = "Select name,crdate,filename from master..sysdatabases";
                DataSet ds_schema_name = SqlDbHelper.Query(get_schema_name_sql, null);//执行语句
                if (ds_schema_name.Tables[0].Rows.Count != 0)
                {
                    // 获取数据库sqlite存储的mysql数据库
                    string sql = "  select * from SqlInfo where   1=1   ";
                    List<SqlInfo> list = new List<SqlInfo>();
                    SqlInfo SqlInfo = new SqlInfo();
                    list = Data<SqlInfo>.GetInstance().GetModelList(sql, SqlInfo);
                    list = list.Where(o => o.type == "mssql").ToList();



                    foreach (var arow in list)
                    {
                        string cname = arow.Mdbname;
                        accessmysqls.Add(cname);
                    }

                    
                    //遍历数据
                  
                    DataTable dt_schema_name = ds_schema_name.Tables[0];
                    //遍历行
                    foreach (DataRow dr in dt_schema_name.Rows)
                    {
                        if (dr["name"].ToString().ToLower() == "master" || dr["name"].ToString().ToLower() == "model" || dr["name"].ToString().ToLower() == "tempdb" || dr["name"].ToString().ToLower() == "msdb" || dr["name"].ToString().ToLower() == "distribution")
                        {
                            continue;
                        }

                        sqlinfo = new SqlServerInfo();

                        sqlinfo.Sqluser = "";
                        sqlinfo.Sqlname = dr["name"].ToString();//数据库名
                        sqlinfo.Sqlpath = dr["filename"].ToString();//路径
                        sqlinfo.Sqltime = dr["crdate"].ToString();//时间
                        sqlinfo.Sqlpass = "";
                        sqlinfo.Sqlunicode = "";
                        sqlinfo.Sqlmaxsize = "";
                      
                        //获取字符集编码
                        
                        string get_collate_sql = "SELECT COLLATIONPROPERTY('Chinese_PRC_Stroke_CI_AI_KS_WS', 'CodePage') as 'collate'";
                        DataSet ds_collate = SqlDbHelper.Query(get_collate_sql, null);//执行语句
                        if (ds_collate.Tables[0].Rows.Count != 0)
                        {
                            string collate = ds_collate.Tables[0].Rows[0]["collate"].ToString();
                            if (collate == "936")
                            {
                                sqlinfo.Sqlunicode = "简体中文GBK";
                            }
                            if (collate == "950") { sqlinfo.Sqlunicode = "繁体中文BIG5"; }
                            if (collate == "437") { sqlinfo.Sqlunicode = "美国/加拿大英语"; }
                            if (collate == "932") { sqlinfo.Sqlunicode = "日文"; }
                            if (collate == "949") { sqlinfo.Sqlunicode = "韩文"; }
                            if (collate == "866") { sqlinfo.Sqlunicode = "俄文"; }
                            if (collate == "65001") { sqlinfo.Sqlunicode = "unicode UFT-8"; }
                        }
                        
                        // 获取数据库限制大小
                        
                        string get_maxsize_sql = "SELECT max_size/1024*8 as maxsize FROM sys.master_files WHERE DB_NAME(database_id) IN(@sqlname)";
                        System.Data.SqlClient.SqlParameter[] msqlparameters = new System.Data.SqlClient.SqlParameter[1];
                        msqlparameters[0] = new System.Data.SqlClient.SqlParameter("@sqlname", sqlinfo.Sqlname);
                        DataSet ds_maxsize = SqlDbHelper.Query(get_maxsize_sql, msqlparameters);//执行语句
                        if (ds_maxsize.Tables[0].Rows.Count != 0)
                        {
                            sqlinfo.Sqlmaxsize = ds_maxsize.Tables[0].Rows[0]["maxsize"].ToString();
                        }
                        if (sqlinfo.Sqlmaxsize == "0" || sqlinfo.Sqlmaxsize == "")
                        {
                            sqlinfo.Sqlmaxsize = "无限制";
                        }



                        // 如果sqlite数据库存有该数据直接获取

                        if (accessmysqls.Contains(sqlinfo.Sqlname))
                        {
                            var ritem = list.Where(o => o.Mdbname == sqlinfo.Sqlname).FirstOrDefault();


                            sqlinfo.Sqlpass = ritem.Mpass;
                          
                        }
                       
                      // 查看用户是否存在
                        
                        string get_sqls_sql = "select name from syslogins where name=@sqlname";
                        System.Data.SqlClient.SqlParameter[] sqlparameters = new System.Data.SqlClient.SqlParameter[1];
                        sqlparameters[0] = new System.Data.SqlClient.SqlParameter("@sqlname", sqlinfo.Sqlname);
                        DataSet ds_sqls = SqlDbHelper.Query(get_sqls_sql, sqlparameters);//执行语句
                        if (ds_sqls.Tables[0].Rows.Count != 0)
                        {
                            sqlinfo.Sqluser = sqlinfo.Sqlname;
                        }
                        else
                        {
                            sqlinfo.Sqluser = "未绑定用户";
                            sqlinfo.Sqlpass = "未绑定用户";
                        }
                        if (sqlinfo.Sqlpass == "")
                        {
                            sqlinfo.Sqlpass = "加密";
                        }
                       //
                        // 将数据存储到数据库
                       //
                        if (accessmysqls.Contains(sqlinfo.Sqlname))
                        {
                          //
                           // 更新数据库
                            //


                          

                            string update_mysql_sql = "update SqlInfo set sqlsize=@sqlsize, muser=@muser,mpass=@mpass,mtime=@mtime,municode=@municode,mpath=@mpath where mdbname=@mdbname  and type=@type";


                            SqlInfo updateSqlInfo = new SqlInfo();
                            updateSqlInfo.Muser = sqlinfo.Sqluser;
                            updateSqlInfo.Mpass = sqlinfo.Sqlpass;
                            updateSqlInfo.Mtime = sqlinfo.Sqltime;
                            updateSqlInfo.Municode = sqlinfo.Sqlunicode;

                            updateSqlInfo.Mpath = sqlinfo.Sqlpath;
                            updateSqlInfo.Mdbname = sqlinfo.Sqlname;
                            updateSqlInfo.sqlsize =ZConvert.StrToInt( sqlinfo.Sqlmaxsize);

                            updateSqlInfo.type = "mssql";


                            Data<SqlInfo>.GetInstance().Update(update_mysql_sql, updateSqlInfo);





                        }
                        else
                        {
                            //
                           // 插入数据库
                            


                            SqlInfo insertmysqlinfo = new SqlInfo();
                            insertmysqlinfo.Mdbname = sqlinfo.Sqlname;
                            insertmysqlinfo.Muser = sqlinfo.Sqluser;
                            insertmysqlinfo.Mpass = sqlinfo.Sqlpass;
                            insertmysqlinfo.Mtime = sqlinfo.Sqltime;

                            insertmysqlinfo.Municode = sqlinfo.Sqlunicode;
                            insertmysqlinfo.Mpath = sqlinfo.Sqlpath;
                            insertmysqlinfo.sqlsize = ZConvert.StrToInt(sqlinfo.Sqlmaxsize);


                            insertmysqlinfo.type = "mssql";

                            string query = "INSERT INTO SqlInfo( sqlsize,  Municode,  Mpath,  type,Mdbname,Muser,Mpass,Mtime)VALUES( @sqlsize,  @Municode,  @Mpath,  @type,@Mdbname,@Muser,@Mpass,@Mtime)   ";
                            Data<SqlInfo>.GetInstance().Add(query, insertmysqlinfo);





                        }
                        csqlservers.Add(sqlinfo.Sqlname);//保存数据库名到本地
                    }
                   
                   // 删除不存在的数据库
                   
                    foreach (var arow in accessmysqls)
                    {
                        string cname = arow;
                        if (!csqlservers.Contains(cname))
                        {
                           

                            //不存在删除
                            string delete_access_sql = " mdbname=@mdbname and type=@type ";
                            SqlInfo delSqlInfo = new SqlInfo();
                            delSqlInfo.Mdbname = cname;
                            delSqlInfo.type = "mssql";
                            Data<SqlInfo>.GetInstance().Delete("SqlInfo", delete_access_sql, delSqlInfo);



                        }
                    }
                
                }
            }
            catch (Exception  ex)
            {
                NLogger.Error(ex.ToString());
                throw ex;
            }

          
        }
    }
}
